package com.hroze.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.apache.log4j.Logger;

/**
 * @author zhwang
 * @version 1.0 build 2009.12.18 查询语句工具
 */
public class SqlTool {
	/**
	 * oracle 数据库
	 * 
	 * @param sql
	 * @param start
	 * @param limit
	 * @return
	 */
	public static String getPageSqlbk(String sql, int start, int limit) {
		String before = "select tttt.* from (";
		String after = ") tttt where r>" + start + " and r<=" + (start + limit);
		sql = sql.replaceFirst("select", "select rownum r, ");
		sql = before + sql + after;
		Logger.getRootLogger().info("pageSQL:" + sql);
		return sql;
	}

	/**
	 * 获得oracle sql 分页语句
	 * @param sql
	 * @param start
	 * @param limit
	 * @return
	 */
	public static String getPageSql(String sql, int start, int limit) {
		String before = "select * from (select tt.*,rownum r from (";
		String after = ")  tt)where r>" + start + " and r<=" + (start + limit);
		sql = before + sql + after;
		Logger.getRootLogger().info("pageSQL:" + sql);
		return sql;
	}

	/**
	 * 生成查询sql语句
	 * @param sql
	 * @return
	 */
	public static String getCountSql(String sql) {
		sql = "select count(*) from (" + sql + ")";
		Logger.getRootLogger().info("countSQL:" + sql);
		return sql;

	}

	/**
	 * 查询数据
	 * @param sql
	 * @param con
	 * @return
	 * @throws SQLException
	 */
	public static int getCount(String sql, Connection con) throws SQLException {
		PreparedStatement psp = con.prepareStatement(getCountSql(sql));
		ResultSet rs = psp.executeQuery();
		int size = 0;
		while (rs.next()) {
			size = rs.getInt(1);
			break;
		}
		rs.close();
		psp.close();
		return size;
	}

	/**
	 * mysql 数据库
	 * 
	 * @param sql
	 * @param start
	 * @param limit
	 * @return
	 */
	public static String getPageMsqlSql(String sql, int start, int limit) {
		return sql + " limit " + start + "," + limit;
	}

	/**
	 * 去除查询语句中的order by和from前面的语句，如果没有则直接返回原有的语句。
	 * 
	 * @param hql
	 * @return
	 */
	public static String getCountHql(String hql) {
		if (hql.indexOf("from") != -1) {
			hql = hql.subSequence(hql.indexOf("from"), hql.length()).toString();
		}
		String s = "select count(*) " + hql;
		if (s.indexOf("order") != -1)
			s = s.subSequence(0, s.indexOf("order")).toString();

		return s;
	}

	/**
	 * 不含oder时使用本方法获得计数语句
	 * @param hql
	 * @return
	 */
	public static String getCountHqlWithOutOder(String hql) {
		if (hql.indexOf("from") != -1) {
			hql = hql.subSequence(hql.indexOf("from"), hql.length()).toString();
		}
		String s = "select count(*) " + hql;
		return s;
	}

	/**
	 * 去除order子句和select子句，然后用id去重复计数，
	 * 
	 * @param hql
	 * @param id
	 * @return
	 */
	public static String getCountHql(String hql, String id) {
		String s = "select count(distinct " + id + ") ";
		if (hql.indexOf("order") != -1)
			hql = hql.subSequence(0, hql.indexOf("order")).toString();
		if (hql.indexOf("from") != -1)
			hql = hql.subSequence(hql.indexOf("from"), hql.length()).toString();
		return s + hql;
	}

	public static void main(String[] args) {
		String sql = "select * from sms_student t";
		System.out.println(getPageSql(sql, 0, 10));
	}

}
